package attend.dao.emp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import attend.com.CodeMetrix;

import exception.BaseException;
import util.DBConnection;

public class EmpDao extends DBConnection {
	
	private List<HashMap<String, String>> 		list 		= 	null;

	private StringBuffer 						sql 		= 	new StringBuffer();
	
	private Connection 							con 		= 	null;
	private PreparedStatement 					pstmt 		= 	null;
	private ResultSet 							rs 			= 	null;
	private ResultSet 							rs2 			= 	null;
	
	private String	msg;
	private String	cur_page;
	private String  rtn_msg;

	private int rowCnt;
	
	private HashMap<String, String> 			emp_info;
	
	
	public EmpDao() {}
	
	/**
	 * <pre>
	 * 부서코드로 그룹/팀에 소속한 사원을 조회한다.
	 * </pre>
	 * @param dept_no
	 * @throws BaseException
	 * @author Yang D
	 * @since	2009/02/04
	 */
	public void retrieveEmpByDeptNo(String dept_no) throws BaseException , SQLException {
		
		try {
			
			con = super.getDataSource().getConnection();
			
			sql.setLength(0);
			
			sql.append("		WITH D  AS(                                 					\r\n");
			sql.append("		    SELECT                                  					\r\n");
			sql.append("						D1.DEPT_NO                  					\r\n");
			sql.append("					,	D1.UPPER_DEPT_NO            					\r\n");
			sql.append("					,	E.EMP_NO                    					\r\n");
			sql.append("					,	E.EMP_NM                    					\r\n");
			sql.append("		    FROM                                    					\r\n");
			sql.append("						TB_DEPT D1                  					\r\n");
			sql.append("		        JOIN    TB_EMP E                    					\r\n");
			sql.append("		        ON      D1.DEPT_NO  =   E.FK_DEPT_NO					\r\n");
			sql.append("		)                                           					\r\n");
			sql.append("		SELECT D.EMP_NO , D.EMP_NM                  					\r\n");
			sql.append("		FROM D                                      					\r\n");
			sql.append("		WHERE 1	=	1                               					\r\n");
			sql.append("			AND D.DEPT_NO			LIKE	REPLACE( ? , 'ALL' , '%')	\r\n");
			sql.append("		UNION                                       					\r\n");
			sql.append("		SELECT D.EMP_NO , D.EMP_NM                  					\r\n");
			sql.append("		FROM D                                      					\r\n");
			sql.append("		WHERE 1	=	1                               					\r\n");
			sql.append("			AND D.UPPER_DEPT_NO		LIKE	REPLACE( ? , 'ALL' , '%')	\r\n");
			
			System.out.println("================================================================\r\n");
			System.out.println("부서코드로 그룹에 소속하거나 팀에 소속한 사원들을 조회한다.\r\n" + sql.toString());
			System.out.println("================================================================\r\n");
			
			pstmt = con.prepareStatement(sql.toString());
			
			pstmt.setString(1, dept_no);
			pstmt.setString(2, dept_no);
			
			rs	=	pstmt.executeQuery();
			
			list 	= 	new ArrayList<HashMap<String,String>>();
			
			while (rs.next()) {
				//각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
				//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
				hash_map.put("emp_no" 			, rs.getString("EMP_NO") );	//사번
				hash_map.put("emp_nm"			, rs.getString("EMP_NM") );	//성명
				
				list.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
			}
		} catch (SQLException se){
				se.printStackTrace();
				BaseException dex = new BaseException();                
				dex.setException(se);
				dex.setAppReturnCode("10");
				throw dex;
			} catch(Exception e){
				e.printStackTrace();
				BaseException dex = new BaseException();                
				dex.setException(e);
				dex.setAppReturnCode("20");
				throw dex;
		} finally {
			this.close(con);
			this.close(pstmt);
			this.close(rs);
		}
		
	}
	
	/**
	 * <pre>
	 * 사원관리 결과조회	-ajax-
	 * </pre>
	 * @param hmParam
	 * @throws BaseException
	 */
	public void retrieveEmpListAjax(HashMap<String, String> hmParam) throws BaseException {
		
		//String long_vaca = long_vaca;

		
		try {
			
			con = super.getDataSource().getConnection();
			
			sql.setLength(0);
	
			sql.append("                          SELECT Z.*                                                                                                  \n");
			sql.append("                          FROM (                                                                                                      \n");
			sql.append("                            SELECT ROW_NUMBER() OVER(ORDER BY X.GROUP_NM , X.TEAM_NM , X.EMP_NO)      ROWNUM , X.* , C.CD_NM  RANK_NM    \n");
			sql.append("                            FROM (                                                                                                    \n");
			sql.append("                              SELECT D.* , E.USER_ID , E.EMP_NO , E.EMP_NM , E.RANK_NM   RANK_NO  , E.WORK_YN, ISNULL(E.APPLY_DT,'') AS APPLY_DT, ISNULL(E.RETIRE_DT,'') AS RETIRE_DT,	\n");
			
			
			sql.append("                              (SELECT      \n");
			sql.append("                               CASE        \n");
			sql.append("                               WHEN ( select max ( VACA_SDATE ) as vaca_sdate ) <=  convert(char(10), getdate(), 120) AND ( select max ( VACA_EDATE ) as vaca_edate ) >= convert(char(10), getdate(), 120) THEN 'Y'    \n");		
			sql.append("                               ELSE 'N'        \n");
			sql.append("                               END VACA_YN     \n");
			sql.append("                               FROM TB_LONG_VACATION V WHERE V.EMP_NO = E.EMP_NO ) AS VACA_YN       \n");
			
			
			sql.append("                              FROM                                                                                                    \n");
			sql.append("                              (                                                                                                       \n");
			sql.append("                                  SELECT G.UPPER_DEPT_NO  GROUP_NO                                                                    \n");
			sql.append("                                  ,   G.DEPT_NM   GROUP_NM                                                                            \n");
			sql.append("                                  ,   T.DEPT_NO   TEAM_NO                                                                             \n");
			sql.append("                                  ,   T.DEPT_NM   TEAM_NM                                                                             \n");
			sql.append("                                  FROM                                                                                                \n");
			sql.append("                                  (                                                                                                   \n");
			sql.append("                                      SELECT *                                                                                        \n");
			sql.append("                                      FROM TB_DEPT                                                                                    \n");
			sql.append("                                      WHERE UPPER_DEPT_NO LIKE REPLACE(? , 'ALL' , '%')   --?                                     \n");
			sql.append("                                      AND UPPER_DEPT_NO = DEPT_NO                                                                     \n");
			sql.append("                                  ) G                                                                                                 \n");
			sql.append("                                  JOIN                                                                                                \n");
			sql.append("                                  (                                                                                                   \n");
			sql.append("                                       SELECT *                                                                                       \n");
			sql.append("                                       FROM TB_DEPT                                                                                   \n");
			sql.append("                                       WHERE UPPER_DEPT_NO != DEPT_NO                                                                 \n");
			sql.append("                                       AND DEPT_NO LIKE REPLACE(? , 'ALL' , '%') --?                                             \n");
			sql.append("                                  ) T                                                                                                 \n");
			sql.append("                                  ON G.UPPER_DEPT_NO = T.UPPER_DEPT_NO                                                                \n");
			sql.append("                              ) D                                                                                                     \n");
			sql.append("                              JOIN                                                                                                    \n");
			sql.append("                              TB_EMP E                                                                                                \n");
			sql.append("                              ON D.TEAM_NO = E.FK_DEPT_NO                                                                             \n");
			sql.append("                              AND E.EMP_NO    LIKE REPLACE(? , 'ALL' , '%')   --?                                                 \n");
			sql.append("                              AND E.WORK_YN    LIKE REPLACE(? , 'ALL' , '%')   --?                                                 \n");
			sql.append("                              AND E.EMP_NM LIKE '%'+REPLACE(? , 'ALL' , '')+'%'   --?                                                 \n");
			sql.append("                            ) X                                                                                                       \n");
			sql.append("                            JOIN                                                                                                      \n");
			sql.append("                            TB_COMM_CD C ON X.RANK_NO = C.CD                                                                          \n");
			sql.append("                        )   Z                                                                                                         \n");
			sql.append("			WHERE	ROWNUM BETWEEN 	?	AND		?																				  \r\n");
	
			
			
			
			if( hmParam.get("long_vaca").equals("Y")){			
			sql.append("			AND Z.VACA_YN = 'Y'			 \r\n");				
			}
			
			else if(hmParam.get("long_vaca").equals("N")){		
			sql.append("			AND Z.VACA_YN = 'N'      \r\n");
			}
			
		
			System.out.println("================================================================\r\n");
			System.out.println("사원관리 결과조회.\r\n" + sql.toString());
			System.out.println("================================================================\r\n");

			
			pstmt = con.prepareStatement(sql.toString());

			int idx	=	1;
			
			pstmt.setString(idx++	, hmParam.get("sgroup")		);	//
			pstmt.setString(idx++	, hmParam.get("steam")		);	//
			pstmt.setString(idx++	, hmParam.get("semp_no")	);	//사번
			pstmt.setString(idx++	, hmParam.get("retired")	);	//
			pstmt.setString(idx++	, hmParam.get("empNm")		);	//
			pstmt.setString(idx++	, hmParam.get("start_row")	);	//시작 row
			pstmt.setString(idx++	, hmParam.get("end_row")	);	//끝 row
			
			rs	=	pstmt.executeQuery();
			
			list 	= 	new ArrayList<HashMap<String,String>>();
			
			while (rs.next()) {
				//각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
				//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
				hash_map.put("group_no" 		, rs.getString("GROUP_NO") );		//그룹코드
				hash_map.put("group_nm" 		, rs.getString("GROUP_NM") );		//그룹명
				hash_map.put("team_no" 			, rs.getString("TEAM_NO") );		//팀코드
				hash_map.put("team_nm" 			, rs.getString("TEAM_NM") );		//팀명
				hash_map.put("emp_no" 			, rs.getString("EMP_NO") );			//사번
				hash_map.put("emp_nm"			, rs.getString("EMP_NM") );			//성명
				hash_map.put("user_id"			, rs.getString("USER_ID") );		//사용자아이디
				hash_map.put("rank_no"			, rs.getString("RANK_NO") );		//직위코드
				hash_map.put("rank_nm"			, rs.getString("RANK_NM") );		//직위명
				hash_map.put("apply_dt"			, rs.getString("APPLY_DT") );		//입사일
				hash_map.put("retire_dt"		, rs.getString("RETIRE_DT") );		//퇴사일
				hash_map.put("work_yn"			, rs.getString("WORK_YN"));
				hash_map.put("vaca_yn"			, rs.getString("VACA_YN"));
				
				//hash_map.put("position_no"		, rs.getString("POSITION_NO") );	//직책코드
				//hash_map.put("position_nm"		, rs.getString("POSITION_NM") );	//직책명
				
				list.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
			}
		} catch (SQLException se){
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e){
			e.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			this.close(con);
			this.close(pstmt);
			this.close(rs);
		}
	}

	/**
	 * <pre>
	 * 사원관리 - 사원등록
	 * </pre>
	 * @param hmParam	-	입력받은 parameter
	 * @throws BaseException
	 */
	public void insertEmpInfo(HashMap<String, String> hmParam) throws BaseException {
		
		System.out.println("--------------------->\n"+hmParam.toString());
		
		try {
			con = super.getDataSource().getConnection();
			
			sql.setLength(0);

			sql.append("                            INSERT INTO TB_EMP                                                                                                                              \n");
			sql.append("                                    (       EMP_NO                                                                                                                          \n");
			sql.append("                                    ,       FK_DEPT_NO                                                                                                                      \n");
			sql.append("                                    ,       EMP_NM                                                                                                                          \n");
			sql.append("                                    ,       USER_ID                                                                                                                         \n");
			sql.append("                                    ,       USER_PWD                                                                                                                        \n");
			sql.append("                                    ,       RANK_NM                                                                                                                         \n");
			sql.append("                                    ,       POSITION                                                                                                                        \n");
			sql.append("                                    ,       APPLY_DT                                                                                                                        \n");
			sql.append("                                    ,       RETIRE_DT                                                                                                                       \n");
			sql.append("                                    ,       WORK_YN                                                                                                                         \n");
			sql.append("                                    ,       IN_TIME                                                                                                                         \n");
			sql.append("                                    ,       OUT_TIME                                                                                                                         \n");
			sql.append("                                    )                                                                                                                                       \n");
			sql.append("                                    SELECT  CASE  WHEN MAX(EMP_NO) = NULL OR MAX(EMP_NO) = ''  THEN 100 ELSE MAX(EMP_NO) +1 END EMP_NO                                 \n");
			sql.append("                                    ,       ?                                                                                                                               \n");
			sql.append("                                    ,       ?                                                                                                                               \n");
			sql.append("                                    ,       ?                                                                                                                               \n");
			sql.append("                                    ,       ?                                                                                                                               \n");
			sql.append("                                    ,       ?                                                                                                                               \n");
			sql.append("                                    ,       ?                                                                                                                               \n");
			sql.append("                                    ,       REPLACE(? , '-' , '')                                                                                                           \n");
			sql.append("                                    ,       REPLACE(? , '-' , '')                                                                                                           \n");
			sql.append("                                    ,       ?                                                                                                                               \n");
			sql.append("                                    ,       ?                                                                                                                               \n");
			sql.append("                                    ,       ?                FROM TB_EMP                                                                                                    \n");
			
			System.out.println("================================================================\r\n");
			System.out.println("사원관리 결과조회.\r\n" + sql.toString());
			System.out.println("================================================================\r\n");
			
			pstmt	=	con.prepareStatement(sql.toString());
			
			int idx = 1;
			
			//pstmt.setString(idx++, hmParam.get("emp_no")	);
			pstmt.setString(idx++, hmParam.get("dept_no")	);
			pstmt.setString(idx++, hmParam.get("emp_nm")	);
			pstmt.setString(idx++, hmParam.get("user_id")	);
			pstmt.setString(idx++, hmParam.get("pass")		);
			pstmt.setString(idx++, hmParam.get("rank_no")	);
			pstmt.setString(idx++, hmParam.get("position_no")	);
			pstmt.setString(idx++, hmParam.get("apply_dt")	); 
			pstmt.setString(idx++, hmParam.get("retire_dt")	); 
			pstmt.setString(idx++, hmParam.get("work_yn")	);
			pstmt.setString(idx++, hmParam.get("in_time")	);
			pstmt.setString(idx++, hmParam.get("out_time")	);

			int apply_rows	=	pstmt.executeUpdate();
			
			//Update 정보가 있으면 출력함.
			if (apply_rows > 0) {
				
				sql.setLength(0);
				
				sql.append("\n").append("  		INSERT INTO TB_VACATION                                      	            ");
				sql.append("\n").append("  		      (                                                      	            ");
				sql.append("\n").append("  		          WORK_YEAR, EMP_NO, VACATION_TP, ALLOC_NUM, ALLOC_DT, REMARK, REG_DATE, ALLOC_ID, JOB_TYPE ");
				sql.append("\n").append("  		      )                                                      	            ");
				sql.append("\n").append("  		   SELECT LEFT (APPLY_DT, 4), A.EMP_NO, '2001', 0, CONVERT(VARCHAR(8),GETDATE(),112), '신규사원최초배정', GETDATE(), '1', 'ADMIN'  ");
				sql.append("\n").append("  		     FROM TB_EMP A, (SELECT MAX (EMP_NO) EMP_NO              	            ");
				sql.append("\n").append("  		                       FROM TB_EMP) B                        	            ");
				sql.append("\n").append("  		    WHERE A.EMP_NO = B.EMP_NO                                	            ");
				sql.append("\n").append("  		   UNION                                                     	            ");
				sql.append("\n").append("  		   SELECT LEFT (APPLY_DT, 4), A.EMP_NO, '2002', 0, CONVERT(VARCHAR(8),GETDATE(),112), '신규사원최초배정', GETDATE(), '1', 'ADMIN'  ");
				sql.append("\n").append("  		     FROM TB_EMP A, (SELECT MAX (EMP_NO) EMP_NO              	            ");
				sql.append("\n").append("  		                       FROM TB_EMP) B                        	            ");
				sql.append("\n").append("  		    WHERE A.EMP_NO = B.EMP_NO                                	            ");
				
				pstmt	=	con.prepareStatement(sql.toString());
				pstmt.executeUpdate();
				
				setMsg("사원정보가 입력되었습니다.");
			} else {
				setMsg("사원정보가 이미 입력 되어 \\r\\입력이 되지 않았습니다.");
			}
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e){
			e.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			this.close(con);
			this.close(pstmt);
			this.close(rs);
		}
	}
	

	/**
	 * 사원결과조회
	 * @param hmCondition
	 * @throws BaseException
	 */
	public void retrieveEmplistRowCount(HashMap<String, String> hmCondition) throws BaseException {
		
		try {
			con 	= 	super.getDataSource().getConnection();
			
			sql.setLength(0);
			
			sql.append("			SELECT	COUNT(*)	ROWCNT                             																	  \n");
			sql.append("                          FROM (                                                                                                      \n");
			sql.append("                            SELECT ROW_NUMBER() OVER(ORDER BY EMP_NO)      ROWNUM , X.* , C.CD_NM  RANK_NM                            \n");
			sql.append("                            FROM (                                                                                                    \n");
			sql.append("                              SELECT D.* , E.USER_ID , E.EMP_NO , E.EMP_NM , E.RANK_NM   RANK_NO   , E.WORK_YN ,                      \n");
			
			
			sql.append("                              (SELECT      \n");
			sql.append("                               CASE        \n");
			sql.append("                               WHEN ( select max ( VACA_SDATE ) as vaca_sdate ) <=  convert(char(10), getdate(), 120) AND ( select max ( VACA_EDATE ) as vaca_edate ) >= convert(char(10), getdate(), 120) THEN 'Y'    \n");	
			sql.append("                               ELSE 'N'        \n");
			sql.append("                               END VACA_YN     \n");
			sql.append("                               FROM TB_LONG_VACATION V WHERE V.EMP_NO = E.EMP_NO ) AS VACA_YN       \n");

			
			
			sql.append("                              FROM                                                                                                    \n");
			sql.append("                              (                                                                                                       \n");
			sql.append("                                  SELECT G.UPPER_DEPT_NO  GROUP_NO                                                                    \n");
			sql.append("                                  ,   G.DEPT_NM   GROUP_NM                                                                            \n");
			sql.append("                                  ,   T.DEPT_NO   TEAM_NO                                                                             \n");
			sql.append("                                  ,   T.DEPT_NM   TEAM_NM                                                                             \n");
			sql.append("                                  FROM                                                                                                \n");
			sql.append("                                  (                                                                                                   \n");
			sql.append("                                      SELECT *                                                                                        \n");
			sql.append("                                      FROM TB_DEPT                                                                                    \n");
			sql.append("                                      WHERE UPPER_DEPT_NO LIKE REPLACE(? , 'ALL' , '%')   --?                                     \n");
			sql.append("                                      AND UPPER_DEPT_NO = DEPT_NO                                                                     \n");
			sql.append("                                  ) G                                                                                                 \n");
			sql.append("                                  JOIN                                                                                                \n");
			sql.append("                                  (                                                                                                   \n");
			sql.append("                                       SELECT *                                                                                       \n");
			sql.append("                                       FROM TB_DEPT                                                                                   \n");
			sql.append("                                       WHERE UPPER_DEPT_NO != DEPT_NO                                                                 \n");
			sql.append("                                       AND DEPT_NO LIKE REPLACE(? , 'ALL' , '%') --?                                             \n");
			sql.append("                                  ) T                                                                                                 \n");
			sql.append("                                  ON G.UPPER_DEPT_NO = T.UPPER_DEPT_NO                                                                \n");
			sql.append("                              ) D                                                                                                     \n");
			sql.append("                              JOIN                                                                                                    \n");
			sql.append("                              TB_EMP E                                                                                                \n");
			sql.append("                              ON D.TEAM_NO = E.FK_DEPT_NO                                                                             \n");
			sql.append("                              AND E.EMP_NO    LIKE REPLACE(? , 'ALL' , '%')   --?                                                 	\n");
			sql.append("                              AND E.WORK_YN    LIKE REPLACE(? , 'ALL' , '%')   --?                                                 	\n");
			sql.append("                              AND E.EMP_NM LIKE '%'+REPLACE(? , 'ALL' , '')+'%'   --?                                                 \n");
			sql.append("                            ) X                                                                                                       \n");
			sql.append("                            JOIN                                                                                                      \n");
			sql.append("                            TB_COMM_CD C ON X.RANK_NO = C.CD                                                                          \n");
			sql.append("                        )   Z																										 \n");  

			sql.append("			WHERE	0 = 0																				  \r\n");
	
			
			
			
			//if( hmCondition.get("long_vaca").equals("Y")){			
			//sql.append("			AND Z.VACA_YN = 'Y'			 \r\n");				
			//}
			
			//else if(hmCondition.get("long_vaca").equals("N")){		
			//sql.append("			AND Z.VACA_YN = 'N'      \r\n");
			//}
		
			
			//sql.append("			AND  ROWNUM BETWEEN 	?	AND		?	      \r\n");

			
			
			
			System.out.println("================================================================\r\n");
			System.out.println("사원관리 결과조회-조회건수.\r\n" + sql.toString());
			System.out.println("================================================================\r\n");
			
			
			pstmt	=	con.prepareStatement(sql.toString());
			
			int	idx	=	1;
			//----------------------------params
			pstmt.setString(idx++	, hmCondition.get("sgroup")	);	//그룹번호
			pstmt.setString(idx++	, hmCondition.get("steam")	);	//
			pstmt.setString(idx++	, hmCondition.get("semp_no")		);	//사번
			pstmt.setString(idx++	, hmCondition.get("retired")		);	//사번
			pstmt.setString(idx++	, hmCondition.get("empNm")			);	//사원이름
			//pstmt.setString(idx++	, hmCondition.get("empNm")			);	//입사일
			//pstmt.setString(idx++	, hmCondition.get("empNm")			);	//퇴사일
			//pstmt.setString(idx++	, hmCondition.get("empNm")			);	//재직여부
			
			rs	=	pstmt.executeQuery();
			
			if (rs.next()) {
				rowCnt	=	rs.getInt("rowCnt");
			}
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e){
			e.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			this.close(con);
			this.close(pstmt);
			this.close(rs);
		}
		
	}
	

	/**
	 * <pre>
	 * 사원의 개인정보를 가져온다.
	 * </pre>
	 * @param emp_no
	 * @throws BaseException
	 */
	public void retrieveEmpByEmpNo(String emp_no) throws BaseException {
		
		try {
			con 	= 	super.getDataSource().getConnection();
			
			sql.setLength(0);
			
			sql.append("			SELECT G.DEPT_NO    GROUP_NO									\r\n");
			sql.append("			    ,   G.DEPT_NM   GROUP_NM									\r\n");
			sql.append("			    ,   D.DEPT_NO   TEAM_NO										\r\n");
			sql.append("			    ,   D.DEPT_NM   TEAM_NM										\r\n");
			sql.append("			    ,   E.EMP_NO												\r\n");
			sql.append("			    ,   E.EMP_NM												\r\n");
			sql.append("			    ,   E.USER_ID												\r\n");
			sql.append("			    ,   E.RANK_NM												\r\n");
			sql.append("			    ,   E.POSITION												\r\n");
			sql.append("			    ,   CASE  WHEN APPLY_DT IS NULL OR APPLY_DT = ''			\r\n");
			sql.append("					THEN	APPLY_DT										\r\n");
			sql.append("					ELSE	SUBSTRING(E.APPLY_DT , 1,4)+'-' +				\r\n");
			sql.append("							SUBSTRING(E.APPLY_DT , 5,2)+'-' +				\r\n");
			sql.append("							SUBSTRING(E.APPLY_DT , 7,2)						\r\n");
			sql.append("					END	 APPLY_DT											\r\n");
			sql.append("			    ,   CASE  WHEN RETIRE_DT IS NULL OR RETIRE_DT = ''			\r\n");
			sql.append("					THEN RETIRE_DT											\r\n");
			sql.append("					ELSE	SUBSTRING(RETIRE_DT , 1,4)+'-' +				\r\n");
			sql.append("							SUBSTRING(RETIRE_DT , 5,2)+'-' +				\r\n");
			sql.append("							SUBSTRING(RETIRE_DT , 7,2)						\r\n");
			sql.append("					END  RETIRE_DT											\r\n");
			sql.append("			    ,   SUBSTRING(E.IN_TIME	, 1,3) AS IN_TIME_1		\r\n");
			sql.append("			    ,   SUBSTRING(E.IN_TIME	, 5,2) AS IN_TIME_2		\r\n");
			sql.append("			    ,   SUBSTRING(E.OUT_TIME	, 1,3) AS OUT_TIME_1		\r\n");
			sql.append("			    ,   SUBSTRING(E.OUT_TIME	, 5,2) AS OUT_TIME_2		\r\n");
			sql.append("			    ,   WORK_YN													\r\n");
			sql.append("			FROM TB_EMP E JOIN TB_DEPT D 									\r\n");
			sql.append("			        ON E.FK_DEPT_NO = D.DEPT_NO								\r\n");
			sql.append("			            AND E.EMP_NO = ?									\r\n");
			sql.append("			        JOIN TB_DEPT G ON G.DEPT_NO = D.UPPER_DEPT_NO			\r\n");

			System.out.println("================================================================\r\n");
			System.out.println("사원관리 결과조회-조회건수3.\r\n" + sql.toString());
			System.out.println("================================================================\r\n");
			
			
			pstmt	=	con.prepareStatement(sql.toString());
			
			int	idx	=	1;
			//----------------------------params
			pstmt.setString(idx++	, emp_no		);	//사번
			//----------------------------
			
			rs	=	pstmt.executeQuery();
			
			emp_info	=	new HashMap<String, String>();
			
			while (rs.next()) {
				emp_info.put("group_no"		,	rs.getString("GROUP_NO"		));
				emp_info.put("group_nm"		,	rs.getString("GROUP_NM"		));
				emp_info.put("team_no"		,	rs.getString("TEAM_NO"		));
				emp_info.put("team_nm"		,	rs.getString("TEAM_NM"		));
				emp_info.put("emp_no"		,	rs.getString("EMP_NO"		));
				emp_info.put("emp_nm"		,	rs.getString("EMP_NM"		));
				emp_info.put("user_id"		,	rs.getString("USER_ID"		));
				emp_info.put("rank_nm"		,	rs.getString("RANK_NM"		));
				emp_info.put("position"		,	rs.getString("POSITION"		));
				emp_info.put("apply_dt"		,	rs.getString("APPLY_DT"		));
				emp_info.put("retire_dt"	,	rs.getString("RETIRE_DT"	));
				emp_info.put("work_yn"		,	rs.getString("WORK_YN"		));
				emp_info.put("in_time_1"	,	rs.getString("IN_TIME_1"		));
				emp_info.put("in_time_2"	,	rs.getString("IN_TIME_2"		));
				emp_info.put("out_time_1"	,	rs.getString("OUT_TIME_1"		));
				emp_info.put("out_time_2"	,	rs.getString("OUT_TIME_2"		));
				//emp_info.put("vaca_yn"	,	rs.getString("WORK_YN"	));
			}
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e){
			e.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			this.close(con);
			this.close(pstmt);
			this.close(rs);
		}
		
	}
	
	/**
	 * <b>사원정보 수정</b>
	 * <code>updateEmpInfo</code>
	 * @param hmCondition
	 * @throws BaseException
	 */
	public void updateEmpInfo(HashMap<String, String> hmCondition) throws BaseException {
		System.out.println("########################################################################################");
		System.out.println( hmCondition.toString() );
		
		try {
			con = super.getDataSource().getConnection();

			sql.setLength(0);

			sql.append("		UPDATE                           			\r\n");
			sql.append("		        TB_EMP                   			\r\n");
			sql.append("		   SET                           			\r\n");
			sql.append("		        FK_DEPT_NO      =       ?			\r\n");
			sql.append("		    ,   EMP_NM          =       ?			\r\n");
			sql.append("		    ,   USER_ID         =       ?			\r\n");
			sql.append("		    ,   RANK_NM         =       ?			\r\n");
			sql.append("		    ,   POSITION        =       ?			\r\n");
			sql.append("		    ,   APPLY_DT        =       ?			\r\n");
			sql.append("		    ,   RETIRE_DT       =       ?			\r\n");
			sql.append("		    ,   IN_TIME        =        ?			\r\n");
			sql.append("		    ,   OUT_TIME       =        ?			\r\n");
			sql.append("		    ,   WORK_YN         =       ?			\r\n");
			sql.append("		 WHERE                           			\r\n");
			sql.append("		        EMP_NO          =       ?			\r\n");

			System.out
					.println("================================================================\r\n");
			System.out.println("사원관리 -사원정보 수정-\r\n" + sql.toString());
			System.out
					.println("================================================================\r\n");

			pstmt = con.prepareStatement(sql.toString());

			int idx = 1;
			//----------------------------params
			pstmt.setString(idx++, hmCondition.get("dept_no")); 	//부서
			pstmt.setString(idx++, hmCondition.get("emp_nm")); 		//성명
			pstmt.setString(idx++, hmCondition.get("user_id")); 	//id
			pstmt.setString(idx++, hmCondition.get("rank_no")); 	//직위
			pstmt.setString(idx++, hmCondition.get("position_no")); //직책
			pstmt.setString(idx++, hmCondition.get("apply_dt").replace("-", ""));	//입사일
			pstmt.setString(idx++, hmCondition.get("retire_dt").replace("-", "")); 	//퇴사일
			pstmt.setString(idx++, hmCondition.get("in_time")); 	//출근시간
			pstmt.setString(idx++, hmCondition.get("out_time")); //퇴근시간
			
			//if (hmCondition.get("retire_dt") != null && !hmCondition.get("retire_dt").equals("") ) {
//				pstmt.setString(idx++, "N"); 	//근무여부
//			} else {
				pstmt.setString(idx++, hmCondition.get("work_yn")); 	//근무여부
//			}
			
			pstmt.setInt(idx++	, Integer.valueOf(hmCondition.get("emp_no"))); 		//사번
			//----------------------------

			int i  = pstmt.executeUpdate();

			if (i > 0) {
				setMsg(CodeMetrix.OK);
			}
			
			System.out.println(msg + "			~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~"	);

		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			e.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			this.close(con);
			this.close(pstmt);
			this.close(rs);
		}
		
	}
	
	/**
	 * <pre>사번으로 부서권한을 조회한다.</pre>
	 * @param emp_no
	 * @throws BaseException
	 */
	public void retrieveEmpVacation(String emp_no) throws BaseException {
		
		try {
			con = super.getDataSource().getConnection();

			sql.setLength(0);

			sql.append("					SELECT  *, reg_date    	\r\n");
			//sql.append("                    CASE  WHEN REG_DATE IS NULL OR REG_DATE = ''			\r\n");
			//sql.append(" 								THEN	CONVERT(VARCHAR(10),GETDATE(),120)										\r\n");
			//sql.append(" 								ELSE	REG_DATE		\r\n");
			//sql.append(" 								ELSE	SUBSTRING(REG_DATE , 1,4)+'-' +				\r\n");
			//sql.append(" 										SUBSTRING(REG_DATE , 5,2)+'-' +				\r\n");
			//sql.append(" 										SUBSTRING(REG_DATE , 7,2)						\r\n");
			//sql.append(" 								END	 REG_DATE				                             \r\n");
			sql.append("					FROM	TB_LONG_VACATION	                		\r\n");
			sql.append("								WHERE EMP_NO	=	?        			\r\n");

			
			System.out.println("================================================================\r\n");
			System.out.println("사원관리 결과조회-조회건수1.\r\n" + sql.toString());
			System.out.println("================================================================\r\n");

			pstmt = con.prepareStatement(sql.toString());

			int idx = 1;
			//----------------------------params
			pstmt.setInt(idx++, Integer.valueOf(emp_no)); //사번
			//----------------------------

			rs = pstmt.executeQuery();

			list 	= 	new ArrayList<HashMap<String,String>>();

			while (rs.next()) {
				
				//각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				hash_map.put("seq"		, rs.getString("SEQ"));
				hash_map.put("emp_no"		, rs.getString("EMP_NO"));
				hash_map.put("vaca_kind"		, rs.getString("VACA_KIND"));
				hash_map.put("vaca_sdate"		, rs.getString("VACA_SDATE"));
				hash_map.put("vaca_edate"		, rs.getString("VACA_EDATE"));
				hash_map.put("vaca_remark"		, rs.getString("VACA_REMARK"));
				hash_map.put("reg_date"		, rs.getString("REG_DATE"));
				
				list.add(hash_map);
			}

		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			e.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			this.close(con);
			this.close(pstmt);
			this.close(rs);
		}
		
	}
	
	
	/**
	 * <pre>체크된 seq로 휴직정보 가져옴.</pre>
	 * @param emp_no
	 * @throws BaseException
	 */
	public void retrieveEmpVacation(String emp_no, String ch_seq_no ) throws BaseException {
		
		try {
			con = super.getDataSource().getConnection();

			sql.setLength(0);

			sql.append("					SELECT  *, reg_date    	\r\n");
			//sql.append("                    CASE  WHEN REG_DATE IS NULL OR REG_DATE = ''			\r\n");
			//sql.append(" 								THEN	CONVERT(VARCHAR(10),GETDATE(),120)										\r\n");
			//sql.append(" 								ELSE	REG_DATE		\r\n");
			//sql.append(" 								ELSE	SUBSTRING(REG_DATE , 1,4)+'-' +				\r\n");
			//sql.append(" 										SUBSTRING(REG_DATE , 5,2)+'-' +				\r\n");
			//sql.append(" 										SUBSTRING(REG_DATE , 7,2)						\r\n");
			//sql.append(" 								END	 REG_DATE				                             \r\n");
			sql.append("					FROM	TB_LONG_VACATION	                		\r\n");
			sql.append("								WHERE EMP_NO	=	?        			\r\n");
			sql.append("								AND SEQ	=	?        			\r\n");

			
			System.out.println("================================================================\r\n");
			System.out.println("사원관리 결과조회-조회건수1.\r\n" + sql.toString());
			System.out.println("================================================================\r\n");

			pstmt = con.prepareStatement(sql.toString());

			int idx = 1;
			//----------------------------params
			pstmt.setInt(idx++, Integer.valueOf(emp_no)); //사번
			pstmt.setInt(idx++, Integer.valueOf(ch_seq_no)); //시퀀스
			//----------------------------

			rs = pstmt.executeQuery();

			list 	= 	new ArrayList<HashMap<String,String>>();

			while (rs.next()) {
				
				//각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				hash_map.put("seq"		, rs.getString("SEQ"));
				hash_map.put("emp_no"		, rs.getString("EMP_NO"));
				hash_map.put("vaca_kind"		, rs.getString("VACA_KIND"));
				hash_map.put("vaca_sdate"		, rs.getString("VACA_SDATE"));
				hash_map.put("vaca_edate"		, rs.getString("VACA_EDATE"));
				hash_map.put("vaca_remark"		, rs.getString("VACA_REMARK"));
				hash_map.put("reg_date"		, rs.getString("REG_DATE"));
				
				list.add(hash_map);
			}

		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			e.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			this.close(con);
			this.close(pstmt);
			this.close(rs);
		}
		
	}



	/**
	 * <pre>사번으로 부서권한을 조회한다.</pre>
	 * @param emp_no
	 * @throws BaseException
	 */
	public void retrieveEmpDeptAuth(String emp_no) throws BaseException {
		
		try {
			con = super.getDataSource().getConnection();

			sql.setLength(0);

			sql.append("					SELECT                                      								\r\n");
			sql.append("							A.AUTH_SEQ                          								\r\n");
			sql.append("						,	D.UPPER_DEPT_NO		GROUP_NO        								\r\n");
			sql.append("						,	G.DEPT_NM			GROUP_NM        								\r\n");
			sql.append("						,	D.DEPT_NO			TEAM_NO         								\r\n");
			sql.append("						,	D.DEPT_NO			STEAM         									\r\n");
			sql.append("						,	D.DEPT_NM			TEAM_NM         								\r\n");
			sql.append("						,	E.EMP_NO                            								\r\n");
			sql.append("						,	E.EMP_NM                            								\r\n");
			sql.append("						,	A.APPROV_YN                         								\r\n");
			sql.append("						,	A.USE_YN                            								\r\n");
			sql.append("					FROM	TB_AUTH_DEPT	A                   								\r\n");
			sql.append("							JOIN TB_EMP		E                   								\r\n");
			sql.append("								ON A.EMP_NO		=	E.EMP_NO									\r\n");
			sql.append("								AND E.EMP_NO	=	?        									\r\n");
			sql.append("							JOIN TB_DEPT	D                   								\r\n");
			sql.append("								ON A.DEPT_NO	=	 D.DEPT_NO  								\r\n");
			sql.append("							JOIN TB_DEPT G                      								\r\n");
			sql.append("								ON D.UPPER_DEPT_NO	= G.DEPT_NO									\r\n");
			
			System.out.println("================================================================\r\n");
			System.out.println("사원관리 결과조회-조회건수2.\r\n" + sql.toString());
			System.out.println("================================================================\r\n");

			pstmt = con.prepareStatement(sql.toString());

			int idx = 1;
			//----------------------------params
			pstmt.setInt(idx++, Integer.valueOf(emp_no)); //사번
			//----------------------------

			rs = pstmt.executeQuery();

			list 	= 	new ArrayList<HashMap<String,String>>();

			while (rs.next()) {
				
				//각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				hash_map.put("auth_seq"		, rs.getString("AUTH_SEQ"));
				hash_map.put("group_no"		, rs.getString("GROUP_NO"));
				hash_map.put("group_nm"		, rs.getString("GROUP_NM"));
				hash_map.put("team_no"		, rs.getString("TEAM_NO"));
				hash_map.put("team_nm"		, rs.getString("TEAM_NM"));
				hash_map.put("emp_no"		, rs.getString("EMP_NO"));
				hash_map.put("emp_nm"		, rs.getString("EMP_NM"));
				hash_map.put("approv_yn"	, rs.getString("APPROV_YN"));
				hash_map.put("use_yn"		, rs.getString("USE_YN"));
				
				list.add(hash_map);
			}

		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			e.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			this.close(con);
			this.close(pstmt);
			this.close(rs);
		}
		
	}

	

	/**
	 * <pre>사원테이블에서 사원을 조회함.</pre>
	 * @param emp_no			-	사번
	 * @throws BaseException
	 */
	public void searchEmpInfo(String emp_no) throws BaseException {
		
		try {

			con 	= 	getDataSource().getConnection(); //근태관리 Connection
			
			sql.setLength(0);
			
			sql.append("		SELECT  										\r\n");
			sql.append("			EMP_NO, FK_DEPT_NO, EMP_NM, USER_ID	  		\r\n");
			sql.append("		FROM TB_EMP										\r\n");
			sql.append("		WHERE EMP_NO 	= 	" + emp_no + "\r\n");

			System.out.println("======================================\r\n");
			System.out.println("사원테이블에서 사원을 조회하는 쿼리실행 내용\r\n" + sql.toString());
			System.out.println("======================================\r\n");

			pstmt 	= 	con.prepareStatement(sql.toString());
			rs 		= 	pstmt.executeQuery();
			
			System.out.println("사원정보조회 start>>>>>>>>>>>>>>>>>>>>>");
			System.out.println(sql.toString());
			System.out.println("사원정보조회 end>>>>>>>>>>>>>>>>>>>>>");

			emp_info	=	new HashMap<String, String>();
			
			while (rs.next()) {

				emp_info.put("emp_no"		, rs.getString("EMP_NO")); 		//
				emp_info.put("dept_no"		, rs.getString("FK_DEPT_NO")); 	//
				emp_info.put("emp_nm"		, rs.getString("EMP_NM")); 		//
				emp_info.put("user_id"		, rs.getString("USER_ID")); 	//

			}

		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();
			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
		
	}
	
	/**
	 * <pre>사원의 휴직정보 저장함.</pre>
	 * @param a_seq			-	스퀀스
	 * @param a_emp_no		-	사번
	 * @param a_team_no		-	부서번호
	 * @param approv_yn		-	권한여부
	 * @throws BaseException
	 */

	public void saveEmpVacation222(int emp_no , String a_seq , String[] a_emp_no , String[] a_vaca_sdate , String[] a_vaca_edate , String[] a_vaca_kind , String[] a_reg_date , String[] a_vaca_remark) throws BaseException {
		//널일 경우 초기화
		if (a_seq == null) {
			a_seq = "0";
		}
		
		int chk_duplicate = 0;
		
		System.out.println("saveEmpVacation############################################################################################");
		System.out.println("emp_no>>>>>>>>"+emp_no);
		System.out.println("a_seq>>>>>>>>>"+a_seq);
		System.out.println(java.util.Arrays.toString(a_emp_no) + "길이:  " + a_emp_no.length);
		System.out.println(java.util.Arrays.toString(a_vaca_sdate)+ "길이:  " + a_vaca_sdate.length);
		System.out.println(java.util.Arrays.toString(a_vaca_edate)+ "길이:  " + a_vaca_edate.length);
		System.out.println(java.util.Arrays.toString(a_vaca_kind)+ "길이:  " + a_vaca_kind.length);
		System.out.println(java.util.Arrays.toString(a_reg_date)+ "길이:  " + a_reg_date.length);
		System.out.println(java.util.Arrays.toString(a_vaca_remark)+ "길이:  " + a_vaca_remark.length);
		System.out.println("############################################################################################");
		
		try {

			con = getDataSource().getConnection(); //근태관리 Connection
			
			sql.setLength(0);
			
			//중복된 부서관한 막음.
			sql.append("                         SELECT                                                    				\r\n");
			sql.append("                                    COUNT(*)     CNT                                  			\r\n");
			sql.append("                         FROM                                                      				\r\n");
			sql.append("                                    TB_LONG_VACATION                                   				\r\n");
			sql.append("                         WHERE                                                     				\r\n");
			sql.append("                                    EMP_NO  = '0'                       				\r\n");
			//sql.append("                         AND                                                       				\r\n");
			
			//ALL일 경우 0으로 치환함.
			//sql.append("                                    DEPT_NO = " + a_team_no[a_team_no.length - 1].replace(CodeMetrix.ALL, "0")+ "           \r\n");
			
			System.out.println("--------------------------------------------------------------------------------");
			System.out.println("중복된 데이터가 존재하는지 검사한다.\r\n" + sql.toString());
			System.out.println("--------------------------------------------------------------------------------");
			
			pstmt	=	con.prepareStatement(sql.toString());
			
			rs	=	pstmt.executeQuery();
			
			
			if (rs.next()) {
				chk_duplicate	=	rs.getInt("CNT");
			}

			if (chk_duplicate > 1) {
				
				
				sql.setLength(0);
	
				sql.append("			DELETE FROM												\r\n");
				sql.append("						TB_LONG_VACATION								\r\n");
				sql.append("			WHERE													\r\n");
				sql.append("						SEQ IN (	" + a_seq + "	)			\r\n");
	
				System.out.println("======================================\r\n");
				System.out.println("쿼리실행 내용\r\n" + sql.toString());
				System.out.println("======================================\r\n");
				
				/**************************************************************************/
				//T	R	A	N	S	A	C	T	I	O	N				S T A R T
				/**************************************************************************/
				con.setAutoCommit(false); //default value ture
	
				pstmt 	= 	con.prepareStatement(sql.toString());
	
				pstmt.executeUpdate();
				
				sql.setLength(0);
				
				sql.append("		 				INSERT INTO              											\r\n");
				sql.append("		 							TB_LONG_VACATION 											\r\n");
				sql.append("		 				           (	         											\r\n");
				sql.append("		 								EMP_NO   											\r\n");
				sql.append("		 				           ,	VACA_SDATE  											\r\n");
				sql.append("		 				           ,	VACA_EDATE											\r\n");
				sql.append("		 				           ,	VACA_KIND											\r\n");
				sql.append("		 				           ,	REG_DATE											\r\n");
				sql.append("		 				           ,	VACA_REMARK											\r\n");
				sql.append("		 							)            											\r\n");
				sql.append("		 				     VALUES              											\r\n");
				sql.append("		 				           (             											\r\n");
				sql.append("		 								?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 							)            											\r\n");
				
				System.out.println("======================================\r\n");
				System.out.println("쿼리실행 내용\r\n" + sql.toString());
				System.out.println("======================================\r\n");
				
				pstmt	=	con.prepareStatement(sql.toString());

				for (int i = 0; i < a_vaca_kind.length; i++) {

					int idx = 1;
					
					pstmt.setString(idx++	, 	a_emp_no	[i]		); //
					pstmt.setString(idx++	, 	a_vaca_sdate	[i]		); //
					pstmt.setString(idx++	, 	a_vaca_edate	[i]		); //
					pstmt.setString(idx++	, 	a_vaca_kind	[i]		); //
					pstmt.setString(idx++	, 	a_reg_date	[i]		); //
					pstmt.setString(idx++	, 	a_vaca_remark	[i]		); //

					pstmt.addBatch();
				}
	
				int[] row = pstmt.executeBatch();	//batch exeucte
				
				System.out.println(row[0] + "				###########################################################");
				
				
								
				if (row[0] < 1) {
					setMsg("실패");
					msg = CodeMetrix.FAILE;
					con.rollback();
				} else {
					setMsg("휴직정보가 저장되었습니다.");
					msg = CodeMetrix.OK;
					//입력할 데이터가 있는 경우 커밋함.
					con.commit();
					con.setAutoCommit(true);
				}
				
				System.out.println(msg + "			!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");

			} else {
				
				
				sql.setLength(0);
	
				sql.append("			DELETE FROM												\r\n");
				sql.append("						TB_LONG_VACATION								\r\n");
				sql.append("			WHERE													\r\n");
				sql.append("						SEQ = 	" + a_seq + "				\r\n");
	
				System.out.println("======================================\r\n");
				System.out.println("쿼리실행 내용\r\n" + sql.toString());
				System.out.println("======================================\r\n");
				
				/**************************************************************************/
				//T	R	A	N	S	A	C	T	I	O	N				S T A R T
				/**************************************************************************/
				con.setAutoCommit(false); //default value ture
	
				pstmt 	= 	con.prepareStatement(sql.toString());
	
				pstmt.executeUpdate();
				
				sql.setLength(0);
				
				sql.append("		 				INSERT INTO              											\r\n");
				sql.append("		 							TB_LONG_VACATION 											\r\n");
				sql.append("		 				           (	         											\r\n");
				sql.append("		 								EMP_NO   											\r\n");
				sql.append("		 				           ,	VACA_SDATE  											\r\n");
				sql.append("		 				           ,	VACA_EDATE											\r\n");
				sql.append("		 				           ,	VACA_KIND											\r\n");
				sql.append("		 				           ,	REG_DATE											\r\n");
				sql.append("		 				           ,	VACA_REMARK											\r\n");
				sql.append("		 							)            											\r\n");
				sql.append("		 				     VALUES              											\r\n");
				sql.append("		 				           (             											\r\n");
				sql.append("		 								?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 							)            											\r\n");
				
				System.out.println("======================================\r\n");
				System.out.println("쿼리실행 내용\r\n" + sql.toString());
				System.out.println("======================================\r\n");
				
				pstmt	=	con.prepareStatement(sql.toString());

				for (int i = 0; i < a_vaca_kind.length; i++) {
					//팀정보를 입력하지 않았을 경우 for문을 빠져나감.
					//if (a_team_no[i] == null || a_team_no[i].equals("ALL")) {
					//	break;
					//}
	
					int idx = 1;
					
					pstmt.setString(idx++	, 	a_emp_no	[i]		); //
					pstmt.setString(idx++	, 	a_vaca_sdate	[i]		); //
					pstmt.setString(idx++	, 	a_vaca_edate	[i]		); //
					pstmt.setString(idx++	, 	a_vaca_kind	[i]		); //
					pstmt.setString(idx++	, 	a_reg_date	[i]		); //
					pstmt.setString(idx++	, 	a_vaca_remark	[i]		); //

					pstmt.addBatch();
				}
	
				int[] row = pstmt.executeBatch();	//batch exeucte
				
				System.out.println(row[0] + "				###########################################################");
								
				if (row[0] < 1) {
					setMsg("실패.");
					msg = CodeMetrix.FAILE;
					con.rollback();
				} else {
					setMsg("휴직정보가 저장되었습니다.");
					msg = CodeMetrix.OK;
					//입력할 데이터가 있는 경우 커밋함.
					con.commit();
					con.setAutoCommit(true);
				}
				
				System.out.println(msg + "			!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
			}
			
		} catch (SQLException se) {

			try {
				con.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
			} // rollback하여 작업된 것을 취소한다..
			
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();
			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
	}

	
	/**
	 * <pre>사원의 휴직정보 저장함.</pre>
	 * @param a_seq			-	스퀀스
	 * @param a_emp_no		-	사번
	 * @param a_team_no		-	부서번호
	 * @param approv_yn		-	권한여부
	 * @throws BaseException
	 */

	public void saveEmpVacation(int emp_no , String a_seq , String[] a_emp_no , String[] a_vaca_sdate , String[] a_vaca_edate , String[] a_vaca_kind , String[] a_reg_date , String[] a_vaca_remark) throws BaseException {
		//널일 경우 초기화
		if (a_seq == null) {
			a_seq = "0";
		}
		
		int chk_duplicate = 0;
		
		
		try {

			con = getDataSource().getConnection(); //근태관리 Connection
			
			sql.setLength(0);
			
			//중복된 부서관한 막음.
			sql.append("       SELECT                                                   \r\n");
			sql.append("          COUNT(*)     CNT                                  	\r\n");
			sql.append("       FROM                                                     \r\n");
			sql.append("          TB_LONG_VACATION                                   	\r\n");
			sql.append("             WHERE                                              \r\n");
			sql.append("            	EMP_NO = " + emp_no + "                       \r\n");	
			sql.append("            	AND                                             \r\n");	
			sql.append("                 ( (VACA_SDATE <= '" + a_vaca_sdate[0] + "' AND  VACA_EDATE >= '" + a_vaca_sdate[0] + "')   \r\n");
			sql.append("            	 OR                                           \r\n");	
			sql.append("                 (VACA_SDATE <= '" + a_vaca_edate[0] + "' AND  VACA_EDATE >= '" + a_vaca_edate[0] + "') )   \r\n");
			
			//ALL일 경우 0으로 치환함.
			//sql.append("                                    DEPT_NO = " + a_team_no[a_team_no.length - 1].replace(CodeMetrix.ALL, "0")+ "           \r\n");
			
			System.out.println("--------------------------------------------------------------------------------");
			System.out.println("중복된 데이터가 존재하는지 검사한다.\r\n" + sql.toString());
			System.out.println("--------------------------------------------------------------------------------");
			
			pstmt	=	con.prepareStatement(sql.toString());
			
			rs	=	pstmt.executeQuery();
			
			
			if (rs.next()) {
				chk_duplicate	=	rs.getInt("CNT");
			}

			if (chk_duplicate > 0) {
				
				setMsg(CodeMetrix.DUPLICATE);
				
				System.out.println(getMsg() + "             ------------------ 중복체크 메시지 설정!!!!!!!!!");
				
			}else{

				sql.setLength(0);
				
				sql.append("		 				INSERT INTO              											\r\n");
				sql.append("		 							TB_LONG_VACATION 											\r\n");
				sql.append("		 				           (	         											\r\n");
				sql.append("		 								EMP_NO   											\r\n");
				sql.append("		 				           ,	VACA_SDATE  											\r\n");
				sql.append("		 				           ,	VACA_EDATE											\r\n");
				sql.append("		 				           ,	VACA_KIND											\r\n");
				sql.append("		 				           ,	REG_DATE											\r\n");
				sql.append("		 				           ,	VACA_REMARK											\r\n");
				sql.append("		 							)            											\r\n");
				sql.append("		 				     VALUES              											\r\n");
				sql.append("		 				           (             											\r\n");
				sql.append("		 								?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 							)            											\r\n");
				
				System.out.println("======================================\r\n");
				System.out.println("쿼리실행 내용\r\n" + sql.toString());
				System.out.println("======================================\r\n");
				
				pstmt	=	con.prepareStatement(sql.toString());
				
				
				for (int i = 0; i < a_vaca_kind.length; i++) {
					//팀정보를 입력하지 않았을 경우 for문을 빠져나감.
					//if (a_team_no[i] == null || a_team_no[i].equals("ALL")) {
					//	break;
					//}
	
					int idx = 1;
					
					pstmt.setString(idx++	, 	a_emp_no	[i]		); //
					pstmt.setString(idx++	, 	a_vaca_sdate	[i]		); //
					pstmt.setString(idx++	, 	a_vaca_edate	[i]		); //
					pstmt.setString(idx++	, 	a_vaca_kind	[i]		); //
					pstmt.setString(idx++	, 	a_reg_date	[i]		); //
					pstmt.setString(idx++	, 	a_vaca_remark	[i]		); //

					pstmt.addBatch();
				}
	
				int[] row = pstmt.executeBatch();	//batch exeucte
				
				System.out.println(row[0] + "				###########################################################");
								
				if (row[0] < 1) {
					//setMsg("실패.");
					//msg = CodeMetrix.FAILE;
					setMsg(CodeMetrix.FAILE);
				}else {
					System.out.println("여기 안옴??");
					//setMsg("휴직정보가 저장되었습니다.");
					//setMsg(CodeMetrix.DUPLICATE);
					setMsg(CodeMetrix.OK);
					//입력할 데이터가 있는 경우 커밋함.
					//con.commit();
					//con.setAutoCommit(true);
				}

			
				
				System.out.println(msg + "			!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
				//setMsg(CodeMetrix.DUPLICATE);
				
				//System.out.println(getMsg() + "             -------------------------          중복체크 메시지 설정");
			}
			
			
		} catch (SQLException se) {

			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();
			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
	}
	
	
	/**
	 * <pre>사원의 휴직정보 수정함.</pre>
	 * @param a_seq			-	스퀀스
	 * @param a_emp_no		-	사번
	 * @param a_team_no		-	부서번호
	 * @param approv_yn		-	권한여부
	 * @throws BaseException
	 */

	public void updateEmpVacation(int emp_no , String a_seq , String[] a_emp_no , String[] a_vaca_sdate , String[] a_vaca_edate , String[] a_vaca_kind , String[] a_reg_date , String[] a_vaca_remark) throws BaseException {
		//널일 경우 초기화
		if (a_seq == null) {
			a_seq = "0";
		}
		
		int chk_duplicate = 0;
		
		
		try {

			con = getDataSource().getConnection(); //근태관리 Connection
			
			sql.setLength(0);
			
			//중복된 부서관한 막음.
			sql.append("       SELECT                                                   \r\n");
			sql.append("          COUNT(*)     CNT                                  	\r\n");
			sql.append("       FROM                                                     \r\n");
			sql.append("          TB_LONG_VACATION                                   	\r\n");
			sql.append("             WHERE                                              \r\n");
			sql.append("            	EMP_NO = " + emp_no + "                       \r\n");
			sql.append("            	AND SEQ != " + a_seq + "                       \r\n");
			sql.append("            	AND                                             \r\n");	
			sql.append("                 ( (VACA_SDATE <= '" + a_vaca_sdate[0] + "' AND  VACA_EDATE >= '" + a_vaca_sdate[0] + "')   \r\n");
			sql.append("            	 OR                                           \r\n");	
			sql.append("                 (VACA_SDATE <= '" + a_vaca_edate[0] + "' AND  VACA_EDATE >= '" + a_vaca_edate[0] + "') )   \r\n");
			
			//ALL일 경우 0으로 치환함.
			//sql.append("                                    DEPT_NO = " + a_team_no[a_team_no.length - 1].replace(CodeMetrix.ALL, "0")+ "           \r\n");
			
			System.out.println("--------------------------------------------------------------------------------");
			System.out.println("중복된 데이터가 존재하는지 검사한다.\r\n" + sql.toString());
			System.out.println("--------------------------------------------------------------------------------");
			
			pstmt	=	con.prepareStatement(sql.toString());
			
			rs	=	pstmt.executeQuery();
			
			
			if (rs.next()) {
				chk_duplicate	=	rs.getInt("CNT");
			}

			if (chk_duplicate > 0) {
				
				//setMsg(CodeMetrix.DUPLICATE);
				setMsg(CodeMetrix.DUPLICATE);
				
				System.out.println(getMsg() + "             ------------------ 중복체크 메시지 설정!!!!!!!!!");
				
			}else{

				sql.setLength(0);
				
				sql.append("                UPDATE TB_LONG_VACATION                                       \n");
				sql.append("                SET    vaca_sdate     =   '"+a_vaca_sdate[0]+"'           \n");
				sql.append("                ,   vaca_edate     =   '"+a_vaca_edate[0]+"'          \n");
				sql.append("                ,    vaca_kind     =   '"+a_vaca_kind[0]+"'         \n");
				sql.append("                ,    reg_date     =   '"+a_reg_date[0]+"'           \n");
				sql.append("                ,    vaca_remark     =   '"+a_vaca_remark[0]+"'          \n");

				sql.append("                WHERE                                               \n");
				sql.append("                        EMP_NO      =   '"+emp_no+"'              \n");
				sql.append("                        AND SEQ  =   '"+a_seq+"'           \n");
				
				/*sql.setLength(0);
				
				sql.append("                UPDATE TB_LONG_VACATION                                       \n");
				sql.append("                SET    vaca_sdate     =   ?           \n");
				sql.append("                SET    vaca_edate     =   ?           \n");
				sql.append("                SET    vaca_kind     =   ?          \n");
				sql.append("                SET    reg_date     =   ?           \n");
				sql.append("                SET    vaca_remark     =   ?           \n");

				sql.append("                WHERE                                               \n");
				sql.append("                        EMP_NO      =   ?              \n");
				sql.append("                        AND SEQ  =   '"+a_seq+"'           \n");
				*/
				
				System.out.println("======================================\r\n");
				System.out.println("쿼리실행 내용\r\n" + sql.toString());
				System.out.println("======================================\r\n");
				
				pstmt	=	con.prepareStatement(sql.toString());
				
				int row = pstmt.executeUpdate();
				
				
				/*for (int i = 0; i < a_vaca_kind.length; i++) {
					//팀정보를 입력하지 않았을 경우 for문을 빠져나감.
					//if (a_team_no[i] == null || a_team_no[i].equals("ALL")) {
					//	break;
					//}
	
					int idx = 1;
					
					//pstmt.setString(idx++	, 	a_emp_no	[i]		); //
					pstmt.setString(idx++	, 	a_vaca_sdate	[i]		); //
					pstmt.setString(idx++	, 	a_vaca_edate	[i]		); //
					pstmt.setString(idx++	, 	a_vaca_kind	[i]		); //
					pstmt.setString(idx++	, 	a_reg_date	[i]		); //
					pstmt.setString(idx++	, 	a_vaca_remark	[i]		); //
					pstmt.setString(idx++	, 	a_emp_no	[i]		); //


					pstmt.addBatch();
				}*/
	
				//int[] row = pstmt.executeBatch();	//batch exeucte
				
				System.out.println(row + "				###########################################################");
								
				if (row < 1) {
					//setMsg("실패.");
					//msg = CodeMetrix.FAILE;
					setMsg(CodeMetrix.FAILE);
				}else {
					System.out.println("여기 안옴??");
					//setMsg("휴직정보가 저장되었습니다.");
					//setMsg(CodeMetrix.DUPLICATE);
					setMsg(CodeMetrix.OK);
					//입력할 데이터가 있는 경우 커밋함.
					//con.commit();
					//con.setAutoCommit(true);
				}

			
				
				System.out.println(msg + "			!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
				//setMsg(CodeMetrix.DUPLICATE);
				
				//System.out.println(getMsg() + "             -------------------------          중복체크 메시지 설정");
			}
			
			
		} catch (SQLException se) {

			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();
			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
	}
	

	
	/**
	 * <pre>사원의 휴직정보 수정함.</pre>
	 * @param a_seq			-	스퀀스
	 * @param a_emp_no		-	사번
	 * @param a_team_no		-	부서번호
	 * @param approv_yn		-	권한여부
	 * @throws BaseException
	 */

	public void updateEmpVacation222222222(int emp_no , String a_seq , String[] a_emp_no , String[] a_vaca_sdate , String[] a_vaca_edate , String[] a_vaca_kind , String[] a_reg_date , String[] a_vaca_remark) throws BaseException {
		//널일 경우 초기화
		if (a_seq == null) {
			a_seq = "0";
		}
		
		int chk_duplicate = 0;
		
		System.out.println("############################################################################################");
		System.out.println("emp_no>>>>>>>>"+emp_no);
		System.out.println("a_seq>>>>>>>>>"+a_seq);
		System.out.println(java.util.Arrays.toString(a_emp_no) + "길이:  " + a_emp_no.length);
		System.out.println(java.util.Arrays.toString(a_vaca_sdate)+ "길이:  " + a_vaca_sdate.length);
		System.out.println(java.util.Arrays.toString(a_vaca_edate)+ "길이:  " + a_vaca_edate.length);
		System.out.println(java.util.Arrays.toString(a_vaca_kind)+ "길이:  " + a_vaca_kind.length);
		System.out.println(java.util.Arrays.toString(a_reg_date)+ "길이:  " + a_reg_date.length);
		System.out.println(java.util.Arrays.toString(a_vaca_remark)+ "길이:  " + a_vaca_remark.length);
		System.out.println("############################################################################################");
		
		try {

			con = getDataSource().getConnection(); //근태관리 Connection
			
			sql.setLength(0);
			
			//중복된 부서관한 막음.
			sql.append("                         SELECT                                                    				\r\n");
			sql.append("                                    COUNT(*)     CNT                                  			\r\n");
			sql.append("                         FROM                                                      				\r\n");
			sql.append("                                    TB_LONG_VACATION                                   				\r\n");
			sql.append("                         WHERE                                                     				\r\n");
			sql.append("                                    EMP_NO  = " + emp_no + "                       				\r\n");
			//sql.append("                         AND                                                       				\r\n");
			
			//ALL일 경우 0으로 치환함.
			//sql.append("                                    DEPT_NO = " + a_team_no[a_team_no.length - 1].replace(CodeMetrix.ALL, "0")+ "           \r\n");
			
			System.out.println("--------------------------------------------------------------------------------");
			System.out.println("중복된 데이터가 존재하는지 검사한다.\r\n" + sql.toString());
			System.out.println("--------------------------------------------------------------------------------");
			
			pstmt	=	con.prepareStatement(sql.toString());
			
			rs	=	pstmt.executeQuery();
			
			
			if (rs.next()) {
				chk_duplicate	=	rs.getInt("CNT");
			}

			if (chk_duplicate > 1) {
				
				
				sql.setLength(0);
	
				sql.append("			DELETE FROM												\r\n");
				sql.append("						TB_LONG_VACATION								\r\n");
				sql.append("			WHERE													\r\n");
				sql.append("						SEQ IN (	" + a_seq + "	)			\r\n");
	
				System.out.println("======================================\r\n");
				System.out.println("쿼리실행 내용\r\n" + sql.toString());
				System.out.println("======================================\r\n");
				
				con.setAutoCommit(false); //default value ture
	
				pstmt 	= 	con.prepareStatement(sql.toString());
	
				pstmt.executeUpdate();
				
				sql.setLength(0);
				
				sql.append("		 				INSERT INTO              											\r\n");
				sql.append("		 							TB_LONG_VACATION 											\r\n");
				sql.append("		 				           (	         											\r\n");
				sql.append("		 								EMP_NO   											\r\n");
				sql.append("		 				           ,	VACA_SDATE  											\r\n");
				sql.append("		 				           ,	VACA_EDATE											\r\n");
				sql.append("		 				           ,	VACA_KIND											\r\n");
				sql.append("		 				           ,	REG_DATE											\r\n");
				sql.append("		 				           ,	VACA_REMARK											\r\n");
				sql.append("		 							)            											\r\n");
				sql.append("		 				     VALUES              											\r\n");
				sql.append("		 				           (             											\r\n");
				sql.append("		 								?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 							)            											\r\n");
				
				System.out.println("======================================\r\n");
				System.out.println("쿼리실행 내용\r\n" + sql.toString());
				System.out.println("======================================\r\n");
				
				pstmt	=	con.prepareStatement(sql.toString());

				for (int i = 0; i < a_vaca_kind.length; i++) {
					//팀정보를 입력하지 않았을 경우 for문을 빠져나감.
					//if (a_team_no[i] == null || a_team_no[i].equals("ALL")) {
					//	break;
					//}
	
					int idx = 1;
					
					pstmt.setString(idx++	, 	a_emp_no	[i]		); //
					pstmt.setString(idx++	, 	a_vaca_sdate	[i]		); //
					pstmt.setString(idx++	, 	a_vaca_edate	[i]		); //
					pstmt.setString(idx++	, 	a_vaca_kind	[i]		); //
					pstmt.setString(idx++	, 	a_reg_date	[i]		); //
					pstmt.setString(idx++	, 	a_vaca_remark	[i]		); //

					pstmt.addBatch();
				}
	
				int[] row = pstmt.executeBatch();	//batch exeucte
				
				System.out.println(row[0] + "				###########################################################");
				
				
	
				if (row[0] < 1) {
					//setMsg("실패");
					msg = CodeMetrix.FAILE;
					con.rollback();
				} else {
					setMsg("휴직정보가 수정되었습니다.");
					//setMsg(CodeMetrix.OK);
					//입력할 데이터가 있는 경우 커밋함.
					con.commit();
					con.setAutoCommit(true);
				}
				
				System.out.println(msg + "			!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
				//setMsg(CodeMetrix.DUPLICATE);
				
				//System.out.println(getMsg() + "             -------------------------          중복체크 메시지 설정");
			} else {
				
				
				sql.setLength(0);
	
				sql.append("			DELETE FROM												\r\n");
				sql.append("						TB_LONG_VACATION								\r\n");
				sql.append("			WHERE													\r\n");
				sql.append("						SEQ = 	" + a_seq + "				\r\n");
	
				System.out.println("======================================\r\n");
				System.out.println("쿼리실행 내용\r\n" + sql.toString());
				System.out.println("======================================\r\n");
				
				/**************************************************************************/
				//T	R	A	N	S	A	C	T	I	O	N				S T A R T
				/**************************************************************************/
				con.setAutoCommit(false); //default value ture
	
				pstmt 	= 	con.prepareStatement(sql.toString());
	
				pstmt.executeUpdate();
				
				sql.setLength(0);
				
				sql.append("		 				INSERT INTO              											\r\n");
				sql.append("		 							TB_LONG_VACATION 											\r\n");
				sql.append("		 				           (	         											\r\n");
				sql.append("		 								EMP_NO   											\r\n");
				sql.append("		 				           ,	VACA_SDATE  											\r\n");
				sql.append("		 				           ,	VACA_EDATE											\r\n");
				sql.append("		 				           ,	VACA_KIND											\r\n");
				sql.append("		 				           ,	REG_DATE											\r\n");
				sql.append("		 				           ,	VACA_REMARK											\r\n");
				sql.append("		 							)            											\r\n");
				sql.append("		 				     VALUES              											\r\n");
				sql.append("		 				           (             											\r\n");
				sql.append("		 								?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 							)            											\r\n");
				
				System.out.println("======================================\r\n");
				System.out.println("쿼리실행 내용\r\n" + sql.toString());
				System.out.println("======================================\r\n");
				
				pstmt	=	con.prepareStatement(sql.toString());

				for (int i = 0; i < a_vaca_kind.length; i++) {
					//팀정보를 입력하지 않았을 경우 for문을 빠져나감.
					//if (a_team_no[i] == null || a_team_no[i].equals("ALL")) {
					//	break;
					//}
	
					int idx = 1;
					
					pstmt.setString(idx++	, 	a_emp_no	[i]		); //
					pstmt.setString(idx++	, 	a_vaca_sdate	[i]		); //
					pstmt.setString(idx++	, 	a_vaca_edate	[i]		); //
					pstmt.setString(idx++	, 	a_vaca_kind	[i]		); //
					pstmt.setString(idx++	, 	a_reg_date	[i]		); //
					pstmt.setString(idx++	, 	a_vaca_remark	[i]		); //

					pstmt.addBatch();
				}
	
				int[] row = pstmt.executeBatch();	//batch exeucte
				
				System.out.println(row[0] + "				###########################################################");
								
				if (row[0] < 1) {
					msg = CodeMetrix.FAILE;
					con.rollback();
				} else {
					//setMsg(CodeMetrix.OK);
					setMsg("사원 휴직정보가 수정되었습니다.");
					//입력할 데이터가 있는 경우 커밋함.
					con.commit();
					con.setAutoCommit(true);
				}
				
				System.out.println(msg + "			!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
			}
			
		} catch (SQLException se) {

			try {
				con.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
			} // rollback하여 작업된 것을 취소한다..
			
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();
			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
	}
	
	
	/**
	 * 사원 휴직정보 수정 (수정후)
	 * @since 2010/01/25
	 * 
	 * @throws BaseException
	 * @throws SQLException
	 */
	public void update_EmpVacation_p(List<String> listCondition) throws BaseException , SQLException {
		
		System.out.println("-------------------여기여기 일단 여기 삭제임!!!!!---------------------------------------");
		//System.out.println("파라미터 정보:");
		
		//for (int i = 0; i < listCondition.size(); i++) {
		//	System.out.println(listCondition.get(i));
		//}
		
		//System.out.println("alloc_id : "+alloc_id);
		//System.out.println("----------------------------------------------------------");
		
		con = super.getDataSource().getConnection();
		
		try {
			/**************************************************************************/
			//T	R	A	N	S	A	C	T	I	O	N				S T A R T
			/**************************************************************************/
			con.setAutoCommit(false);				//default value ture
			
			sql.setLength(0);	//sql초기화
			
			sql.append("\n").append(" DELETE TB_LONG_VACATION ");
			sql.append("\n").append(" WHERE SEQ = ? ");
			
            pstmt = con.prepareStatement(sql.toString());

			String[] arrStr = listCondition.get(0).split(",");	//사번을 배열로 넣음.
			
			
			for (int i = 0; i < arrStr.length; i++) {
				
				int idx = 1;
				
				//순서 주의
				//전체 선택하여 삭제시 맨 앞에 on이라는 텍스트가 붙어서 옴.
				//여기서 체크하여 on이라는 텍스트가 들어가지 못하게 처리한다.
				//즉 on 아닐때만 쿼리문을 실행하게끔 한다.
				if(!arrStr[i].equals("on")){
					pstmt.setInt(	idx++ , Integer.parseInt(arrStr[i]));				//SEQ
							
					pstmt.addBatch();
				}
			
			}

			System.out.println("------------------------------------------------------\r\n");
			System.out.println("휴가삭제 - 삭제\r\n" + sql.toString());
			System.out.println("------------------------------------------------------\r\n");
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		E   N   D
			/**************************************************************************/
			
			int[] i = pstmt.executeBatch();	//batch exeucte
			
			/**************************************************************************/
			//T	R	A	N	S	A	C	T	I	O	N				C O M M I T
			/**************************************************************************/
			con.commit();
			
			for (int j : i) {

//				System.out.println(j);
				
				if (j > 0) {
					//update가 적용된 row가 0 이하인 경우
					//setRtn_msg("휴직정보가 삭제되었습니다.");
					//msg = CodeMetrix.DELETE;
					//setMsg(CodeMetrix.DELETE);
					setMsg("삭제되었습니다.");
				} else {
					//setMsg(CodeMetrix.OK);
					setMsg("ERROR가 발생하였습니다.");
				}
			}
			
			System.out.println(rtn_msg);
			
		} catch (SQLException se) {
			/**************************************************************************/
			//T	R	A	N	S	A	C	T	I	O	N				R O L  L B A C K
			/**************************************************************************/
			con.rollback();   // rollback하여 작업된 것을 취소한다..
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();

			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
	}
	
	
	/**
	 * 사원 휴직정보 삭제 DELETE
	 * @since 2010/01/25
	 * 
	 * @throws BaseException
	 * @throws SQLException
	 */
	public void deleteEmpVacation_p(List<String> listCondition) throws BaseException , SQLException {
		
		System.out.println("-------------------여기여기 일단 여기 삭제임!!!!!---------------------------------------");
		//System.out.println("파라미터 정보:");
		
		//for (int i = 0; i < listCondition.size(); i++) {
		//	System.out.println(listCondition.get(i));
		//}
		
		//System.out.println("alloc_id : "+alloc_id);
		//System.out.println("----------------------------------------------------------");
		
		con = super.getDataSource().getConnection();
		
		try {
			/**************************************************************************/
			//T	R	A	N	S	A	C	T	I	O	N				S T A R T
			/**************************************************************************/
			con.setAutoCommit(false);				//default value ture
			
			sql.setLength(0);	//sql초기화
			
			sql.append("\n").append(" DELETE TB_LONG_VACATION ");
			sql.append("\n").append(" WHERE SEQ = ? ");
			
            pstmt = con.prepareStatement(sql.toString());

			String[] arrStr = listCondition.get(0).split(",");	//사번을 배열로 넣음.
			
			
			for (int i = 0; i < arrStr.length; i++) {
				
				int idx = 1;
				
				//순서 주의
				//전체 선택하여 삭제시 맨 앞에 on이라는 텍스트가 붙어서 옴.
				//여기서 체크하여 on이라는 텍스트가 들어가지 못하게 처리한다.
				//즉 on 아닐때만 쿼리문을 실행하게끔 한다.
				if(!arrStr[i].equals("on")){
					pstmt.setInt(	idx++ , Integer.parseInt(arrStr[i]));				//SEQ
							
					pstmt.addBatch();
				}
			
			}

			System.out.println("------------------------------------------------------\r\n");
			System.out.println("휴가삭제 - 삭제\r\n" + sql.toString());
			System.out.println("------------------------------------------------------\r\n");
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		E   N   D
			/**************************************************************************/
			
			int[] i = pstmt.executeBatch();	//batch exeucte
			
			/**************************************************************************/
			//T	R	A	N	S	A	C	T	I	O	N				C O M M I T
			/**************************************************************************/
			con.commit();
			
			for (int j : i) {

//				System.out.println(j);
				
				if (j > 0) {
					//update가 적용된 row가 0 이하인 경우
					//setRtn_msg("휴직정보가 삭제되었습니다.");
					//msg = CodeMetrix.DELETE;
					//setMsg(CodeMetrix.DELETE);
					setMsg("삭제되었습니다.");
				} else {
					//setMsg(CodeMetrix.OK);
					setMsg("ERROR가 발생하였습니다.");
				}
			}
			
			System.out.println(rtn_msg);
			
		} catch (SQLException se) {
			/**************************************************************************/
			//T	R	A	N	S	A	C	T	I	O	N				R O L  L B A C K
			/**************************************************************************/
			con.rollback();   // rollback하여 작업된 것을 취소한다..
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();

			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
	}
	
	
	/**
	 * <pre>사원의 부서권한을 저장함.</pre>
	 * @param a_seq			-	스퀀스
	 * @param a_emp_no		-	사번
	 * @param a_team_no		-	부서번호
	 * @param approv_yn		-	권한여부
	 * @throws BaseException
	 */
	public void saveEmpDept(int emp_no , String a_seq , String[] a_emp_no , String[] a_team_no , String[] approv_yn) throws BaseException {
		//널일 경우 초기화
		if (a_seq == null) {
			a_seq = "0";
		}
		
		int chk_duplicate = 0;
		
		System.out.println("############################################################################################");
		System.out.println(java.util.Arrays.toString(a_emp_no) + "길이:  " + a_emp_no.length);
		System.out.println(java.util.Arrays.toString(a_team_no)+ "길이:  " + a_team_no.length);
		System.out.println(java.util.Arrays.toString(approv_yn)+ "길이:  " + approv_yn.length);
		System.out.println("############################################################################################");
		
		System.out.println("%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%");
		System.out.println(a_team_no[a_team_no.length - 1]);
		System.out.println("%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%");
		
		try {

			con = getDataSource().getConnection(); //근태관리 Connection
			
			sql.setLength(0);
			
			//중복된 부서관한 막음.
			sql.append("                         SELECT                                                    				\r\n");
			sql.append("                                    COUNT(*)     CNT                                  			\r\n");
			sql.append("                         FROM                                                      				\r\n");
			sql.append("                                    TB_AUTH_DEPT                                   				\r\n");
			sql.append("                         WHERE                                                     				\r\n");
			sql.append("                                    EMP_NO  = " + emp_no + "                       				\r\n");
			sql.append("                         AND                                                       				\r\n");
			
			//ALL일 경우 0으로 치환함.
			sql.append("                                    DEPT_NO = " + a_team_no[a_team_no.length - 1].replace(CodeMetrix.ALL, "0")+ "           \r\n");
			
			System.out.println("--------------------------------------------------------------------------------");
			System.out.println("중복된 데이터가 존재하는지 검사한다.\r\n" + sql.toString());
			System.out.println("--------------------------------------------------------------------------------");
			
			pstmt	=	con.prepareStatement(sql.toString());
			
			rs	=	pstmt.executeQuery();
			
			
			if (rs.next()) {
				chk_duplicate	=	rs.getInt("CNT");
			}

			if (chk_duplicate > 0) {
				setMsg(CodeMetrix.DUPLICATE);
				
				System.out.println(getMsg() + "             -------------------------          중복체크 메시지 설정");
			} else {
				
				sql.setLength(0);
	
				sql.append("			DELETE FROM												\r\n");
				sql.append("						TB_AUTH_DEPT								\r\n");
				sql.append("			WHERE													\r\n");
				sql.append("						AUTH_SEQ IN (	" + a_seq + "	)			\r\n");
	
				System.out.println("======================================\r\n");
				System.out.println("쿼리실행 내용\r\n" + sql.toString());
				System.out.println("======================================\r\n");

				con.setAutoCommit(false); 

				pstmt 	= 	con.prepareStatement(sql.toString());
	
				pstmt.executeUpdate();
				
				sql.setLength(0);
				
				sql.append("		 				INSERT INTO              											\r\n");
				sql.append("		 							TB_AUTH_DEPT 											\r\n");
				sql.append("		 				           (	         											\r\n");
				sql.append("		 								EMP_NO   											\r\n");
				sql.append("		 				           ,	DEPT_NO  											\r\n");
				sql.append("		 				           ,	APPROV_YN											\r\n");
				sql.append("		 							)            											\r\n");
				sql.append("		 				     VALUES              											\r\n");
				sql.append("		 				           (             											\r\n");
				sql.append("		 								?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 				           ,	?        											\r\n");
				sql.append("		 							)            											\r\n");
				
				System.out.println("======================================\r\n");
				System.out.println("쿼리실행 내용\r\n" + sql.toString());
				System.out.println("======================================\r\n");
				
				pstmt	=	con.prepareStatement(sql.toString());
				
				/************** PARAMETERS START ****************************/
	
				
				for (int i = 0; i < approv_yn.length; i++) {
					//팀정보를 입력하지 않았을 경우 for문을 빠져나감.
					if (a_team_no[i] == null || a_team_no[i].equals("ALL")) {
						break;
					}
	
					int idx = 1;
					
					pstmt.setString(idx++	, 	a_emp_no	[i]		); //
					pstmt.setString(idx++	, 	a_team_no	[i]		); //
					pstmt.setString(idx++	, 	approv_yn	[i]		); //
					
					/************** PARAMETERS E N D ****************************/
					
					pstmt.addBatch();
				}
	
				int[] row = pstmt.executeBatch();	//batch exeucte
				
				System.out.println(row[0] + "				###########################################################");
				
				
				if (row[0]< 1) {
					msg = CodeMetrix.FAILE;
					con.rollback();
				} else {
					msg = CodeMetrix.OK;
					//입력할 데이터가 있는 경우 커밋함.
					con.commit();
					con.setAutoCommit(true);
				}
				
				System.out.println(msg + "			!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!");
			}
			
		} catch (SQLException se) {
			/**************************************************************************/
			//T	R	A	N	S	A	C	T	I	O	N				R O L  L B A C K
			/**************************************************************************/
			try {
				con.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
			} // rollback하여 작업된 것을 취소한다..
			
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();
			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
	}
	
	/**
	 * <pre>비밀번호  초기화</pre>
	 * @param emp_no
	 * @param user_id
	 * @param dept_no
	 * @throws BaseException
	 * @author Yang D
	 * @since	2009/03/13
	 */
	public void initPassword(int emp_no , String user_id , String dept_no) throws BaseException {
		
		try {
			
			con	=	getDataSource().getConnection();
			
			sql.setLength(0);
			
			sql.append("                UPDATE TB_EMP                                       \n");
			sql.append("                SET    USER_PWD     =   '" + user_id + "'           \n");
			sql.append("                WHERE                                               \n");
			sql.append("                        EMP_NO      =   " + emp_no + "              \n");
			sql.append("                    AND FK_DEPT_NO  =   '" + dept_no + "'           \n");

			System.out.println("======================================\r\n");
			System.out.println("비밀번호 초기화\r\n" + sql.toString());
			System.out.println("======================================\r\n");

			pstmt 	= 	con.prepareStatement(sql.toString());
			int cnt = 	pstmt.executeUpdate();


			
			if (cnt > 0) {
				setMsg(CodeMetrix.OK);
			} else {
				setMsg(CodeMetrix.FAILE);
			}

		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();
			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
	}

	/**
	 * <pre>employee ID duplication validation</pre>
	 * @param user_id
	 */
	public void retrieveIdById(String user_id) throws BaseException {
		
		try {
			
			con	=	getDataSource().getConnection();
			
			sql.setLength(0);
			
			sql.append("           SELECT COUNT(*) FROM TB_EMP                \n");
			sql.append("           WHERE  USER_ID = '"+user_id+"'                        \n");

			System.out.println("======================================\r\n");
			System.out.println("사원 아이디 중복 체크 \r\n" + sql.toString());
			System.out.println("======================================\r\n");

			pstmt 	= 	con.prepareStatement(sql.toString());
			rs		=	pstmt.executeQuery();
			
			if (rs.next()) {
				if (rs.getInt(1) == 0) {
					setMsg(CodeMetrix.OK);
				} else {
					setMsg(CodeMetrix.DUPLICATE);
				}
			}

		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch (Exception e) {
			BaseException dex = new BaseException();
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (rs != null)
					rs.close();
				if (con != null)
					con.close();
			} catch (SQLException fse) {
				BaseException dex = new BaseException();
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}
		}
		
	}
	
	/****************************************G E T T E R / S E T T E R	START******************************************/

	public List<HashMap<String, String>> getList() {
		return list;
	}

	public void setList(List<HashMap<String, String>> list) {
		this.list = list;
	}
	
	public String getMsg() {
		return msg;
	}

	public void setMsg(String msg) {
		this.msg = msg;
	}

	public String getCur_page() {
		return cur_page;
	}

	public void setCur_page(String cur_page) {
		this.cur_page = cur_page;
	}

	public int getRowCnt() {
		return rowCnt;
	}

	public void setRowCnt(int rowCnt) {
		this.rowCnt = rowCnt;
	}

	public HashMap<String, String> getEmp_info() {
		return emp_info;
	}

	public void setEmp_info(HashMap<String, String> emp_info) {
		this.emp_info = emp_info;
	}
	
	public String getRtn_msg() {
		return rtn_msg;
	}

	public void setRtn_msg(String rtn_msg) {
		this.rtn_msg = rtn_msg;
	}
	

	/**
	 * @param hmCondition
	 * @return
	 * @throws BaseException 
	 */
	public List<HashMap<String , String>> retrieveEmpExcel(HashMap<String, String> hmParam) throws BaseException {
		
		try {
			
			con = super.getDataSource().getConnection();
			
			sql.setLength(0);

			sql.append("                            SELECT ROW_NUMBER() OVER(ORDER BY X.GROUP_NM , X.TEAM_NM , X.EMP_NO)      ROWNUM , X.* , C.CD_NM  RANK_NM                            \n");
			sql.append("                            FROM (                                                                                                    \n");
			sql.append("                              SELECT D.* , E.USER_ID , E.EMP_NO , E.EMP_NM , E.RANK_NM   RANK_NO  , E.WORK_YN, ISNULL(E.APPLY_DT,'') AS APPLY_DT, ISNULL(E.RETIRE_DT,'') AS RETIRE_DT, \n");					
			sql.append("                              (SELECT      \n");
			sql.append("                               CASE        \n");
			sql.append("                               WHEN ( select max ( VACA_SDATE ) as vaca_sdate ) <=  convert(char(10), getdate(), 120) AND ( select max ( VACA_EDATE ) as vaca_edate ) >= convert(char(10), getdate(), 120) THEN 'Y'    \n");		
			sql.append("                               ELSE 'N'        \n");
			sql.append("                               END VACA_YN     \n");
			sql.append("                               FROM TB_LONG_VACATION V WHERE V.EMP_NO = E.EMP_NO ) AS VACA_YN       \n");	
			sql.append("                              FROM                                                                                                    \n");
			sql.append("                              (                                                                                                       \n");
			sql.append("                                  SELECT G.UPPER_DEPT_NO  GROUP_NO                                                                    \n");
			sql.append("                                  ,   G.DEPT_NM   GROUP_NM                                                                            \n");
			sql.append("                                  ,   T.DEPT_NO   TEAM_NO                                                                             \n");
			sql.append("                                  ,   T.DEPT_NM   TEAM_NM                                                                             \n");
			sql.append("                                  FROM                                                                                                \n");
			sql.append("                                  (                                                                                                   \n");
			sql.append("                                      SELECT *                                                                                        \n");
			sql.append("                                      FROM TB_DEPT                                                                                    \n");
			sql.append("                                      WHERE UPPER_DEPT_NO LIKE REPLACE(? , 'ALL' , '%')   --?                                     \n");
			sql.append("                                      AND UPPER_DEPT_NO = DEPT_NO                                                                     \n");
			sql.append("                                  ) G                                                                                                 \n");
			sql.append("                                  JOIN                                                                                                \n");
			sql.append("                                  (                                                                                                   \n");
			sql.append("                                       SELECT *                                                                                       \n");
			sql.append("                                       FROM TB_DEPT                                                                                   \n");
			sql.append("                                       WHERE UPPER_DEPT_NO != DEPT_NO                                                                 \n");
			sql.append("                                       AND DEPT_NO LIKE REPLACE(? , 'ALL' , '%') --?                                             \n");
			sql.append("                                  ) T                                                                                                 \n");
			sql.append("                                  ON G.UPPER_DEPT_NO = T.UPPER_DEPT_NO                                                                \n");
			sql.append("                              ) D                                                                                                     \n");
			sql.append("                              JOIN                                                                                                    \n");
			sql.append("                              TB_EMP E                                                                                                \n");
			sql.append("                              ON D.TEAM_NO = E.FK_DEPT_NO                                                                             \n");
			sql.append("                              AND E.EMP_NO    LIKE REPLACE(? , 'ALL' , '%')   --?                                                 \n");
			sql.append("                              AND E.WORK_YN    LIKE REPLACE(? , 'ALL' , '%')   --?                                                 \n");
			sql.append("                            ) X                                                                                                       \n");
			sql.append("                            JOIN                                                                                                      \n");
			sql.append("                            TB_COMM_CD C ON X.RANK_NO = C.CD                                                                          \n");
			
			
			if( hmParam.get("long_vaca").equals("Y")){			
				sql.append("			AND Z.VACA_YN = 'Y'			 \r\n");				
				}
				
				else if(hmParam.get("long_vaca").equals("N")){		
				sql.append("			AND Z.VACA_YN = 'N'      \r\n");
				}
			
			System.out.println("================================================================\r\n");
			System.out.println("사원관리 엑셀다운로드...\r\n" + sql.toString());
			System.out.println("================================================================\r\n");
			
			pstmt = con.prepareStatement(sql.toString());

			int idx	=	1;
			
			pstmt.setString(idx++	, hmParam.get("sgroup")		);	//
			pstmt.setString(idx++	, hmParam.get("steam")		);	//
			pstmt.setString(idx++	, hmParam.get("semp_no")	);	//사번
			pstmt.setString(idx++	, hmParam.get("retired")	);	//
			
			rs	=	pstmt.executeQuery();
			
			list 	= 	new ArrayList<HashMap<String,String>>();
			
			while (rs.next()) {
				//각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
				//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
				hash_map.put("group_no" 		, rs.getString("GROUP_NO") );		//그룹코드
				hash_map.put("group_nm" 		, rs.getString("GROUP_NM") );		//그룹명
				hash_map.put("team_no" 			, rs.getString("TEAM_NO") );		//팀코드
				hash_map.put("team_nm" 			, rs.getString("TEAM_NM") );		//팀명
				hash_map.put("emp_no" 			, rs.getString("EMP_NO") );			//사번
				hash_map.put("emp_nm"			, rs.getString("EMP_NM") );			//성명
				hash_map.put("user_id"			, rs.getString("USER_ID") );		//사용자아이디
				hash_map.put("rank_no"			, rs.getString("RANK_NO") );		//직위코드
				hash_map.put("rank_nm"			, rs.getString("RANK_NM") );		//직위명
				hash_map.put("apply_dt"			, rs.getString("APPLY_DT") );		//입사일
				hash_map.put("retire_dt"		, rs.getString("RETIRE_DT") );		//퇴사일
				hash_map.put("work_yn"			, rs.getString("WORK_YN"));
				hash_map.put("vaca_yn"			, rs.getString("VACA_YN"));
				
				//hash_map.put("position_no"		, rs.getString("POSITION_NO") );	//직책코드
				//hash_map.put("position_nm"		, rs.getString("POSITION_NM") );	//직책명
				
				list.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
			}
		} catch (SQLException se){
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e){
			e.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			this.close(con);
			this.close(pstmt);
			this.close(rs);
		}
		
		return list;
		
	}


	/****************************************G E T T E R / S E T T E R	START******************************************/
}
